﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using StockFinder.Model;
using System.Globalization;

namespace StockFinder.DataAccess
{
    public class SymbolDataAccess : DataAccessBase
    {
        private const string DOES_SYMBOL_EXIST_BY_NAME_COMMAND = "DoesSymbolExistByName";
        private const string INSERT_SYMBOL_COMMAND = "InsertSymbol";
        private const string INSERT_SYMBOL_WITH_EXCHANGE_COMMAND = "InsertSymbolWithExchange";
        private const string GET_ALL_SYMBOLS = "GetAllSymbols";
        private const string GET_LIST_OF_SYMBOLS_THAT_HAVE_PRICES = "[dbo].[GetListOfSymbolsThatHavePrices]";
        private const string TRUNCATE_SYMBOL_TABLE = "dbo.TruncateSymbolTable";
        private const string TRUNCATE_SYMBOLS_AND_PRICES = "dbo.TruncateSymbolsAndPrices";

        public static void InsertSymbols(List<string> symbols)
        {
            foreach (string symbol in symbols)
            {
                InsertSymbol(symbol);
            }
        }


        public static void TruncateSymbolTable()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("truncate table dbo.Symbols", connection) { CommandType = System.Data.CommandType.Text})
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static int InsertSymbol(string name)
        {
            int result = -1;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(INSERT_SYMBOL_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Name", name);

                    command.Parameters.Add(new SqlParameter() { ParameterName = "RETURN_VALUE", Direction = System.Data.ParameterDirection.ReturnValue });

                    command.ExecuteNonQuery();

                    result = (int)command.Parameters["RETURN_VALUE"].Value;

                    //symbol.SymbolId = result;
                }
            }

            return result;
        }

        public static int InsertSymbol(string name, string exchange)
        {
            int result = -1;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(INSERT_SYMBOL_WITH_EXCHANGE_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Exchange", name);

                    command.Parameters.Add(new SqlParameter() { ParameterName = "RETURN_VALUE", Direction = System.Data.ParameterDirection.ReturnValue });

                    command.ExecuteNonQuery();

                    result = (int)command.Parameters["RETURN_VALUE"].Value;

                    //symbol.SymbolId = result;
                }
            }

            return result;
        }

        public bool DoesSymbolExistByName(Symbol symbol)
        {
            bool exists = false;

            SqlConnection connection = CreateOpenConnection();
            
            using (SqlCommand command = new SqlCommand(DOES_SYMBOL_EXIST_BY_NAME_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@Name", symbol.Name);

                exists = Convert.ToBoolean(command.ExecuteScalar());
            }           

            return exists;
        }

        public static List<Symbol> GetAllSymbols()
        {
            List<Symbol> symbols = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(GET_ALL_SYMBOLS, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        symbols = new List<Symbol>();

                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                int symbolId = Convert.ToInt32(dataReader["SymbolId"]);
                                string name = Convert.ToString(dataReader["Name"]);
                                DateTime createdDateTime = Convert.ToDateTime(dataReader["CreatedDateTime"]);

                                symbols.Add(new Symbol() { SymbolId = symbolId, Name = name, CreatedDateTime = createdDateTime });
                            }
                        }
                    }
                }
            }

            return symbols;
        }

        public List<Symbol> GetListOfSymbolsThatHavePrices()
        {
            List<Symbol> symbols = null;

            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(GET_LIST_OF_SYMBOLS_THAT_HAVE_PRICES, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    symbols = new List<Symbol>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            int symbolId = Convert.ToInt32(dataReader["SymbolId"]);
                            string name = Convert.ToString(dataReader["Name"]);
                            DateTime createdDateTime = Convert.ToDateTime(dataReader["CreatedDateTime"]);

                            symbols.Add(new Symbol() { SymbolId = symbolId, Name = name, CreatedDateTime = createdDateTime });
                        }
                    }
                }
            }

            return symbols;
        }

        public List<Symbol> GetAllSymbolsAndPrices(DateTime dateFrom)
        {
            List<Symbol> symbols = GetAllSymbols();

            DailyPriceDataAccess dailyPricesDataAccess = new DailyPriceDataAccess();

            foreach (Symbol symbol in symbols)
            {
                //retrieve daily prices
                dailyPricesDataAccess.GetAllDailyPricesBySymbolIdFromDate(symbol, dateFrom);

                //generate weekly prices
                GenerateWeeklyPrices(symbol);
            }

            return symbols;
        }

        /// <summary>
        /// Generates the weekly price data based on daily prices list
        /// </summary>
        /// <param name="symbol"></param>
        public void GenerateWeeklyPrices(Symbol symbol)
        {
            //make sure we have some prices
            if (symbol.Prices != null &&
                symbol.Prices.Count > 0)
            {
                symbol.WeeklyPrices.Clear();

                //loop the daily prices
                foreach (DailyPrice dailyPrice in symbol.Prices)
                {
                    //duff prices?
                    if (dailyPrice.Open == 0)
                    {
                        //yes so quit
                        break;
                    }

                    //get the week of year
                    int weekOfYear = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(
                        dailyPrice.PriceDate,
                        CultureInfo.CurrentCulture.DateTimeFormat.CalendarWeekRule,
                        CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek);

                    //does this week/year combination already exist?
                    WeeklyPrice weeklyPrice = symbol.GetExistingWeeklyPrice(weekOfYear, dailyPrice.PriceDate.Year);

                    //does it exist?
                    if (weeklyPrice == null)
                    {
                        //no so create and setup
                        weeklyPrice = new WeeklyPrice();
                        weeklyPrice.Year = dailyPrice.PriceDate.Year;
                        weeklyPrice.WeekOfYear = weekOfYear;

                        //add to list
                        symbol.WeeklyPrices.Add(weeklyPrice);
                    }

                    //set day of week
                    switch (dailyPrice.PriceDate.DayOfWeek)
                    {
                        case DayOfWeek.Monday:

                            weeklyPrice.Monday = dailyPrice;

                            break;

                        case DayOfWeek.Tuesday:

                            weeklyPrice.Tuesday = dailyPrice;

                            break;

                        case DayOfWeek.Wednesday:

                            weeklyPrice.Wednesday = dailyPrice;

                            break;

                        case DayOfWeek.Thursday:

                            weeklyPrice.Thursday = dailyPrice;

                            break;

                        case DayOfWeek.Friday:

                            weeklyPrice.Friday = dailyPrice;

                            break;

                        default:
                            //do nothing
                            break;
                    }

                }
            }
        }
    }
}
